﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'ufn_UPDMS_Get_Training_Info_By_Training_Id')
	BEGIN
		DROP Function ufn_UPDMS_Get_Training_Info_By_Training_Id
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : ufn_UPDMS_Get_Training_Info_By_Training_Id
**	Desc : 근력운동ID별 운동부위를 조회해서 한 컬럼으로 리턴한다.
**	Test Exec Query : select dbo.ufn_UPDMS_Get_Training_Info_By_Training_Id ('TR00000185', 'AB001', 'TRAINING_SET', 'ko')
**	Called by : usp_UPDMS_FIT_WEIGHT_TRAINING_Select_Paging
**	Program ID : Fit1011m
**	Auth : 송시명
**	Date : 2014-05-22
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE FUNCTION [dbo].[ufn_UPDMS_Get_Training_Info_By_Training_Id]
(
@ls_training_id nvarchar(10),
@ls_training_part nvarchar(5),
@ls_search_type varchar(20),
@ls_lang_set nvarchar(2)
)
RETURNS nvarchar(max)
AS

BEGIN

    DECLARE @rtn nvarchar(max)
    SET @rtn = ''

    IF @ls_search_type = 'TRAINING_PART'
    BEGIN
    SELECT @rtn = CASE WHEN LEN(Rslt) > 0 THEN LEFT(Rslt, LEN(Rslt) - 1) ELSE Rslt END
      FROM (
           SELECT (
                  SELECT DISTINCT C.Training_Part + ',' AS [text()]
                    FROM UPDMS_FIT_WEIGHT_TRAINING_PARTS B WITH(NOLOCK)
                    JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
                   WHERE B.Training_Id = @ls_training_id
                     FOR XML PATH('')
                  ) AS Rslt
           ) Z
    END
    ELSE IF @ls_search_type = 'TRAINING_PART_NM'
    BEGIN
    SELECT @rtn = CASE WHEN LEN(Rslt) > 0 THEN LEFT(Rslt, LEN(Rslt) - 1) ELSE Rslt END
      FROM (
           SELECT (
                  SELECT CASE @ls_lang_set WHEN 'ko' THEN X.Value1 ELSE X.Value2 END + ',' AS [text()]
                    FROM UPDMS_COM_CD X WITH(NOLOCK)
                    JOIN (
                         SELECT DISTINCT C.Training_Part
                           FROM UPDMS_FIT_WEIGHT_TRAINING_PARTS B WITH(NOLOCK)
                           JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
                          WHERE B.Training_Id = @ls_training_id
                         ) Y
                      ON X.Cd = Y.Training_Part
                         AND X.Id = 'FIT'
                     FOR XML PATH('')
                  ) AS Rslt
           ) Z
    END
    ELSE IF @ls_search_type = 'TRAINING_NM'
    BEGIN
    SELECT @rtn = CASE WHEN LEN(Rslt) > 0 THEN LEFT(Rslt, LEN(Rslt) - 1) ELSE Rslt END
      FROM (
           SELECT (
                  SELECT C.Training_Nm + ',' AS [text()]
                    FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
                    JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
                    JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
                   WHERE A.Training_Id = @ls_training_id
                     AND C.Training_Part = @ls_training_part
                     FOR XML PATH('')
                  ) AS Rslt
           ) Z
    END
    ELSE IF @ls_search_type = 'TRAINING_WEIGHT'
    BEGIN
    SELECT @rtn = Total_Weight
      FROM (
           SELECT SUM(B.Action_Weight * B.Action_Count * C.Weight_Calc_Rate * B.Set_Count) AS Total_Weight
             FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
             JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
             JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
            WHERE A.Training_Id = @ls_training_id
           ) Z
    END
    ELSE IF @ls_search_type = 'TRAINING_COUNT'
    BEGIN
    SELECT @rtn = Total_Count
      FROM (
           SELECT SUM(B.Action_Count * B.Set_Count) AS Total_Count
             FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
             JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
             JOIN UPDMS_FIT_TRAINING_TYPE AS C WITH(NOLOCK) ON B.Training_Type_Id = C.Training_Type_Id
            WHERE A.Training_Id = @ls_training_id
           ) Z
    END
    ELSE IF @ls_search_type = 'TRAINING_SET'
    BEGIN
    SELECT @rtn = Total_Set
      FROM (
           SELECT SUM(B.Set_Count) AS Total_Set
             FROM UPDMS_FIT_WEIGHT_TRAINING AS A WITH(NOLOCK)
             JOIN UPDMS_FIT_WEIGHT_TRAINING_PARTS AS B WITH(NOLOCK) ON A.Training_Id = B.Training_Id
            WHERE A.Training_Id = @ls_training_id
           ) Z
    END

    RETURN LTRIM(RTRIM(@rtn))
  
END